package com.caifu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.caifu.bean.HealthyArchiveSearchBean;
import com.caifu.pojo.YlElderlyInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 老人信息表 Mapper 接口
 * </p>
 *
 * @author GZ
 * @since 2020-11-06
 */
public interface YlElderlyInfoMapper  extends BaseMapper<YlElderlyInfo> {

    /**
     *  获取养老院老人 信息列表
     * @param params params
     * @return List
     */
    List<Map<String, Object>> getElderlyInfoPageList(Map<String, Object> params);

    /**
     * 获取养老院老人 信息列表 总数
     * @param params params
     * @return Integer
     */
    Integer getElderlyInfoPageListTotalCount(Map<String, Object> params);

    /**
     *
     * 功能描述：分页查询老人信息列表
     *
     * @Param  searchBean 查询条件
     * @Return
     * @Exception
     * @Author  GZ
     * @Date   2020/11/11 15:53
     */
    List<Map<String, Object>> pagedQueryElderInfoData(HealthyArchiveSearchBean searchBean);
    /**
     *
     * 功能描述：查询老人信息总数
     *
     * @Param searchBean 查询条件
     * @Return
     * @Exception
     * @Author  GZ
     * @Date   2020/11/11 15:57
     */
    Integer queryElderInfoTotalRecords(HealthyArchiveSearchBean searchBean);

    /**
     * 获取居住楼苑信息
     *
     * @param userNo userNo
     * @return String
     */
    Map<String, Object> getBuildingInfoByUserNo(@Param("userNo") String userNo);


    /**
     *   获取生日 的user
     * @param subBirth subBirth
     * @return List
     */
    List<Map<String,Object>> queryElderlyInfoByBirth(@Param("subBirth")String subBirth);
    /**
    * 
    * 功能描述：根据学历查询老人统计信息
    * 
    * @Param
    * @Return      
    * @Exception   
    * @Author  GZ
    * @Date   2020/11/24 18:08
    */
    @Select({"<script>",
            "select l.label_name,",
            "(select count(*) from yl_elderly_info e where e.is_stay_back='1' and e.USER_STATE=1",
            "and e.PENSION_NO=#{pensionNo}",
            "and e.education=l.label_code) as manCount",
            "from ys_label l where l.parent_code=#{parentCode} and l.status!=9",
    "</script>"})
    List<Map<String,Object>> queryElderlyInfoCountByEduction(@Param("pensionNo") String pensionNo,@Param("parentCode") String parentCode);

    /**
     *
     * 功能描述：根据社保类型查询老人统计信息
     *
     * @Param
     * @Return
     * @Exception
     * @Author  GZ
     * @Date   2020/11/24 18:08
     */
    @Select({"<script>",
            "select l.label_name,",
            "(select count(*) from yl_elderly_info e where e.is_stay_back='1' and e.USER_STATE=1",
            "and e.PENSION_NO=#{pensionNo}",
            "and e.SOCIAL_SECURITY_TYPE=l.label_code) as manCount",
            "from ys_label l where l.parent_code=#{parentCode} and l.status!=9 ",
            "</script>"})
    List<Map<String,Object>> queryElderlyInfoCountBySocialInsurance(@Param("pensionNo") String pensionNo,@Param("parentCode") String parentCode);

    /**
     *
     * 功能描述：根据年龄和性别查询老人统计信息
     *
     * @Param pensionNo 养老机构编号  sex 性别
     * @Return
     * @Exception
     * @Author  GZ
     * @Date   2020/11/24 18:08
     */
    @Select({"<script>",
            "select s.STATISTIC_NAME,(select count(*) from yl_elderly_info e",
            " where e.is_stay_back='1' and e.USER_STATE='1'",
            " AND e.USER_SEX=#{sex} and e.PENSION_NO=#{pensionNo}",
            " and e.AGE between s.MIN_AGE and s.MAX_AGE )",
            "AS AGE_COUNT FROM yl_age_statistic s order by s.id ",
            "</script>"})
    List<Map<String,Object>> queryElderlyInfoCountByAgeAndSex(@Param("pensionNo") String pensionNo,@Param("sex") String sex);
    /**
    * 
    * 功能描述：根据护理等级获取老人统计信息
    * 
    * @Param pensionNo 养老机构编号
    * @Return      
    * @Exception   
    * @Author  GZ
    * @Date   2020/11/25 10:07
    */
    @Select({"<script>",
            "SELECT c.COST_NAME,(SELECT count(*) from yl_elderly_info e INNER JOIN yl_nursing_record r",
            "on e.USER_NO = r.USER_NO WHERE r.is_effective='1' and e.is_stay_back='1'",
            " and e.USER_STATE='1' AND r.cost_no =c.cost_no) as man_count",
            "FROM yl_cost c where c.COST_TYPE='102' and c.STATUS='1'",
            "and c.PENSION_NO=#{pensionNo} order by c.CREATE_TIME",
            "</script>"})
    List<Map<String,Object>> queryElderlyInfoCountByNursingDegree(@Param("pensionNo") String pensionNo);
    /**
    * 
    * 功能描述：根据长护险等级获取老人统计信息
    * 
    * @Param pensionNo 养老机构编号
    * @Return      
    * @Exception   
    * @Author  GZ
    * @Date   2020/11/25 10:18
    */
    @Select({"<script>",
            "SELECT c.COST_NAME,(SELECT count(*) from yl_elderly_info e INNER JOIN yl_assessment_record a",
            "on e.USER_NO = a.USER_NO WHERE a.is_effective='1'",
            " and e.is_stay_back='1' and e.USER_STATE='1' and a.cost_no=C.COST_NO ) as man_count",
            "FROM yl_cost c where c.COST_TYPE='103' and c.STATUS='1'",
            "and c.PENSION_NO=#{pensionNo} order by c.CREATE_TIME",
            "</script>"})
    List<Map<String,Object>> queryElderlyInfoCountByAssessmentDegree(@Param("pensionNo") String pensionNo);
    /**
    * 
    * 功能描述：根据生日月份和养老院信息获取老人信息列表
    * 
    * @Param month 生日月份 pension 养老机构编号  maxSize最大条数
    * @Return      
    * @Exception   
    * @Author  GZ
    * @Date   2020/11/25 13:16
    */
    @Select({"<script>",
            "select * from yl_elderly_info e where e.is_stay_back='1'",
            "and SUBSTR(e.birth_date,5 ,2) = #{month}",
            "and E.is_stay_back='1' and E.USER_STATE='1'",
            "AND e.PENSION_NO=#{pensionNo} order by SUBSTR(e.birth_date,5 ,4),USER_NO",
            "<when test='maxSize>0'>",
                "LIMIT 0,#{maxSize}",
            "</when>",
            "</script>"})
    List<YlElderlyInfo> queryUserInfoListByBirthMonth(@Param("month") String month
            ,@Param("pensionNo") String pensionNo,@Param("maxSize") int maxSize);
    /**
    * 
    * 功能描述：查询护理预警信息
    * 
    * @Param pensionNo 养老机构编号   limitDate 过期日期  maxSize 最大条数
    * @Return      
    * @Exception   
    * @Author  GZ
    * @Date   2020/11/25 15:18
    */
    @Select({"<script>",
            "SELECT E.USER_NO,E.USER_NAME,R.EFFECT_DATE,R.next_effect_date",
            "FROM yl_elderly_info E LEFT JOIN yl_nursing_record R",
            "ON E.USER_NO=R.USER_NO WHERE E.PENSION_NO=#{pensionNo} and E.is_stay_back='1' and E.USER_STATE='1'  ",
            "and R.is_effective='1' AND R.next_effect_date &lt;= #{limitDate}",
            "order by R.next_effect_date",
            "<when test='maxSize>0'>",
                "LIMIT 0,#{maxSize}",
            "</when>",
            "</script>"})
    List<Map<String,Object>> queryUserNursingAlertDate(@Param("pensionNo") String pensionNo
            ,@Param("limitDate") String limitDate,@Param("maxSize") int maxSize);

    /**
     *
     * 功能描述：查询评估有预警信息
     *
     * @Param pensionNo 养老机构编号   limitDate 过期日期  maxSize 最大条数
     * @Return
     * @Exception
     * @Author  GZ
     * @Date   2020/11/25 15:18
     */
    @Select({"<script>",
            "SELECT E.USER_NO,E.USER_NAME,R.VALID_DATE FROM yl_elderly_info E LEFT JOIN yl_assessment_record R",
            "ON E.USER_NO=R.USER_NO WHERE R.is_effective='1'",
            " AND R.VALID_DATE &lt;= #{limitDate} and E.PENSION_NO=#{pensionNo}",
            "and E.is_stay_back='1' and e.USER_STATE='1' ",
            "order by R.VALID_DATE",
            "<when test='maxSize>0'>",
                "LIMIT 0,#{maxSize}",
            "</when>",
            "</script>"})
    List<Map<String,Object>> queryUserAssessmentAlertDate(@Param("pensionNo") String pensionNo
            ,@Param("limitDate") String limitDate,@Param("maxSize") int maxSize);

    /**
     *
     * 功能描述：查询合同预警信息
     *
     * @Param pensionNo 养老机构编号   limitDate 过期日期  maxSize 最大条数
     * @Return
     * @Exception
     * @Author  GZ
     * @Date   2020/11/25 15:18
     */
    List<Map<String,Object>> queryUserContractAlertDate(@Param("pensionNo") String pensionNo
            ,@Param("limitDate") String limitDate,@Param("maxSize") int maxSize);
    /**
    * 
    * 功能描述：根据床位编号查询床位信息
    * 
    * @Param bedNo 床位编号
    * @Return      
    * @Exception   
    * @Author  GZ
    * @Date   2020/11/25 18:21
    */
    @Select({"<script>",
            "select e.USER_NAME,e.AGE,e.cognition_status,",
            "(select c.COST_NAME from yl_cost c inner JOIN yl_nursing_record n",
            "on n.cost_no=c.COST_NO where n.is_effective='1' and n.USER_NO=e.USER_NO) as nursingName,",
            "(select c.COST_NAME from yl_cost c inner JOIN yl_assessment_record a on a.cost_no=c.COST_NO ",
            "where a.is_effective='1' and a.USER_NO=e.USER_NO) as assessmentName",
            "from yl_elderly_info e LEFT JOIN yl_bed_record r",
            "on e.USER_NO=r.USER_NO where r.BED_NO=#{bedNo} and r.is_effective='1'",
            " and e.is_stay_back='1' and e.USER_STATE='1'",
            "</script>"})
    Map<String,Object> queryElderlyInfoByBedNo(String bedNo);

    /**
     *  获取 床位记录定时器 需要的userNo
     * @param params params
     * @return String
     */
    List<String> getUserNoForTimerWithBedRecord(Map<String, Object> params);


    /**
     *  获取 护理 记录定时器 需要的userNo
     * @param params params
     * @return String
     */
    List<String> getUserNoForTimerWithNursingRecord(Map<String, Object> params);

    /**
     *  获取 评估 记录定时器 需要的userNo
     * @param params params
     * @return String
     */
    List<String> getUserNoForTimerWithAssessmentRecord(Map<String, Object> params);
}
